package com.leeson.portal.manage.utils;


import java.io.File;
import java.util.Date;
import java.util.Scanner;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 企业级大数据录入-java操作excel
 * @author Tony Liu
 * @version 1.0
 * 2014-08-18
 *
 */
public class OperatorExcel {
	
	/**
	 * 生成一个excle文件
	 * @param fileName
	 */
	public static void writeExcel(String fileName){
		
		WritableWorkbook wwb = null;
		
		try {
			// 创建一个可写入的工作簿（WorkBook）对象
			wwb = Workbook.createWorkbook(new File(fileName));
			
			// 创建一个可写入的工作表 
			// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作簿中的位置
			WritableSheet ws = wwb.createSheet("sheetTest", 0);
			for(int i=0;i<10;i++){
				for(int j=0;j<5;j++){
					Label labelC = new Label(j,i,"第"+(i+1)+"行，第"+(j+1)+"列");
					ws.addCell(labelC);
				}
			}
			
			wwb.write();// 从内从中写入文件中
			wwb.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("生成第一个Excel文件"+fileName+"成功");
	}
	
    /**
     * excel文件的修改
     * @param fileName 文件路径+文件名+文件后缀
     */
	public static void writeConentToExcel(String fileName) throws Exception{
		
		jxl.write.Number n = null;
		jxl.write.DateTime d = null;
		
		File tempFile = new File(fileName);
		WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
		WritableSheet sheet = workbook.createSheet("TestCreateExcel",0);
		
		// 预定义的一些字体和格式， 字形、大小、加粗、倾斜、下划线、颜色
		// 头文件
		WritableFont headerFont = new WritableFont(WritableFont.ARIAL,15,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.GREEN);
		WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
		
		// 标题
		WritableFont titleFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
		WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		
		// 内容
		WritableFont detFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
		WritableCellFormat detFormat = new WritableCellFormat(detFont);
		
		// number 格式
		NumberFormat nf=new NumberFormat("0.000");
		WritableCellFormat priceFormat = new WritableCellFormat(nf);
		
		// 日期
		DateFormat df = new DateFormat("yyyy-MM-dd");
		WritableCellFormat dateFormat = new WritableCellFormat(df);
		
		// 创建单元格
		Label l = new Label(0,0,"文件的头信息",headerFormat);
		sheet.addCell(l);
		
		// 添加标题
		int column=0; //　列
		l = new Label(column++,1,"姓名",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"日期",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"货币单位",titleFormat);
		sheet.addCell(l);
		l = new Label(column++,1,"薪水",titleFormat);
		sheet.addCell(l);
		
		// 添加内容
		int i =0; // 行
		column = 0; // 列
		l = new Label(column++,i+2,"Golden",detFormat);
		sheet.addCell(l);
		d = new DateTime(column++,i+2,new Date(),dateFormat);
		sheet.addCell(d);
		l = new Label(column++,i+2,"￥",detFormat);
		sheet.addCell(l);
		n = new jxl.write.Number(column++,i+2,12000,priceFormat);
		sheet.addCell(n);
		
		i++;
		column = 0; // 列
		l = new Label(column++,i+2,"路上",detFormat);
		sheet.addCell(l);
		d = new DateTime(column++,i+2,new Date(),dateFormat);
		sheet.addCell(d);
		l = new Label(column++,i+2,"￥",detFormat);
		sheet.addCell(l);
		n = new jxl.write.Number(column++,i+2,15000,priceFormat);
		sheet.addCell(n);
		
		i++;
		column = 0; // 列
		l = new Label(column++,i+2,"Mr.Sandman",detFormat);
		sheet.addCell(l);
		d = new DateTime(column++,i+2,new Date(),dateFormat);
		sheet.addCell(d);
		l = new Label(column++,i+2,"￥",detFormat);
		sheet.addCell(l);
		n = new jxl.write.Number(column++,i+2,13000,priceFormat);
		sheet.addCell(n);
		
		// 设置列的宽度
		column=0;
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 10);
		sheet.setColumnView(column++, 20);
		
		workbook.write();
		workbook.close();
		System.out.println("内容写入"+fileName+"成功！");
	}
	
	/**
	 * 读取文件信息
	 * @param fileName
	 */
	public static void readExcelInfo(String fileName)throws Exception{
		
		Workbook book = Workbook.getWorkbook(new File(fileName)); // 构造Workbook（工作簿）对象
		
		Sheet sheet = book.getSheet(0);
		// 得到第一列第一行的单元格
		int columNum = sheet.getColumns(); // 得到所有列
		int rowNum = sheet.getRows(); // 得到所有行
		
		System.out.println("行："+rowNum+"  列："+columNum);
		
		for(int i=0;i<rowNum;i++){// 读取行
			for(int j=0;j<columNum;j++){ //读取列
				Cell cell = sheet.getCell(j, i);
				String result = cell.getContents(); // 得到单元格的值
				System.out.print(result);
				System.out.print("\t");
			}
			System.out.println();
		}
		book.close(); // 关闭对象
	}
	
	public static void main(String[] args){
		try {
			System.out.println("1、创建Excel文件，输入Excel文件名称（包括路径和后缀）");
			Scanner scan = new Scanner(System.in);
			final String fileName = scan.next();// 获得键盘值
			writeExcel(fileName);
				
			//
			System.out.println("2、将内容写入Excel文件：");
			writeConentToExcel(fileName);
			
			// excel文件的导入
			System.out.println("3、读取Excel文件：");
			readExcelInfo(fileName);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
